/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package xml.timgiayto.utils;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import xml.timgiayto.binding.comment.InfoComment;
import xml.timgiayto.binding.comment.InfoComments;
import xml.timgiayto.binding.info.Information;
import xml.timgiayto.binding.info.Informations;

/**
 *
 * @author mrnghiep
 */
public class DBUtils {

    public static Connection makeConnection() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            String url = "jdbc:sqlserver://localhost:1433;DatabaseName=TimGiayTo";
            Connection con = DriverManager.getConnection(url, "sa", "123456");

//            String url = "jdbc:sqlserver://nttfashion.mssql.somee.com";
//            Connection con = DriverManager.getConnection(url, "mrnghiep205_SQLLogin_1", "vbg65ly5b3");
            return con;
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } catch (ClassNotFoundException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        }
        return null;
    }

    /**
     * Function to get list information from database.
     * @return List<Informations>
     */
    public static List<Informations> getInformations() {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            // Make connection
            con = DBUtils.makeConnection();

            // Call procedure getListInfo from SQL
            call = con.prepareCall("{call getInformations()}");
            rs = call.executeQuery();

            Informations informations = new Informations();
            Informations informationsConverted = new Informations();

            // Declare List to store informations
            List<Informations> list = new ArrayList<Informations>();

            // Declare attribute of information object
            int id = 0;
            String fullName = "";
            String address = "";
            String phone = "";
            String email = "";
            String info = "";
            String type = "";
            String location = "";
            String owner = "";
            String imageUrl = "";
            String description = "";
            String date = "";
            String category = "";

            while (rs.next()) {
                id = rs.getInt("id");
                fullName = rs.getString("fullName");
                address = rs.getString("address");
                phone = rs.getString("phone");
                email = rs.getString("email");
                info = rs.getString("info");
                type = rs.getString("type");
                location = rs.getString("location");
                owner = rs.getString("owner");
                imageUrl = rs.getString("imageUrl");
                description = rs.getString("description");

                date = rs.getString("date");
                category = rs.getString("category");

                // New object information have Vietnamese from database.
                Information information = new Information(id, fullName, address, phone, email, info, type, location, owner, imageUrl, description, date, category);
                // New object information unsigned Vietnamese from database.
                Information informationConverted = new Information(id, CommonUtils.convertVietnamese(fullName),
                        CommonUtils.convertVietnamese(address), phone, email, CommonUtils.convertVietnamese(info),
                        CommonUtils.convertVietnamese(type), CommonUtils.convertVietnamese(location),
                        CommonUtils.convertVietnamese(owner), imageUrl, CommonUtils.convertVietnamese(description),
                        date, CommonUtils.convertVietnamese(category));

                informations.getInformation().add(information);
                informationsConverted.getInformation().add(informationConverted);
            }
            // Add to list.
            list.add(informations);
            list.add(informationsConverted);
            return list;
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return null;
    }

    public static boolean createInformation(Information info) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call createInformation(?,?,?,?,?,?,?,?,?,?,?,?)}");
            call.setString(1, info.getFullName());
            call.setString(2, info.getAddress());
            call.setString(3, info.getPhone());
            call.setString(4, info.getEmail());
            call.setString(5, info.getInfo());
            call.setString(6, info.getType());
            call.setString(7, info.getLocation());
            call.setString(8, info.getOwner());
            call.setString(9, info.getImageUrl());
            call.setString(10, info.getDescription());
            call.setString(11, info.getDate());
            call.setString(12, info.getCategory());

            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return false;
    }

    public static InfoComments getComments() {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            // Make connection
            con = DBUtils.makeConnection();

            // Call procedure getListInfo from SQL
            call = con.prepareCall("{call getComments()}");
            rs = call.executeQuery();

            InfoComments infoComments = new InfoComments();

            // Declare attribute of information object
            int id = 0;
            String fullName = "";
            String email = "";
            String content = "";
            String date = "";
            int infoId = 0;

            while (rs.next()) {
                id = rs.getInt("id");
                fullName = rs.getString("fullName");
                email = rs.getString("email");
                content = rs.getString("content");
                date = rs.getString("date");
                infoId = rs.getInt("infoId");
                // New object comment
                InfoComment comment = new InfoComment(id, fullName, email, content, date, infoId);

                infoComments.getInfoComment().add(comment);
            }
            return infoComments;
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return null;
    }

    public static boolean createComment(InfoComment infoComment) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call createComment(?,?,?,?,?)}");
            call.setString(1, infoComment.getFullName());
            call.setString(2, infoComment.getEmail());
            call.setString(3, infoComment.getContent());
            call.setString(4, infoComment.getDate());
            call.setInt(5, infoComment.getInfoId());


            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return false;
    }

    public static boolean deleteInfo(int id) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call deleteInfo(?)}");
            call.setInt(1, id);

            int row = call.executeUpdate();
            if (row > 0) {
                return true;
            }
        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return false;
    }

    public static boolean checkLogin(String email, String password) {
        Connection con = null;
        CallableStatement call = null;
        ResultSet rs = null;
        try {
            con = DBUtils.makeConnection();
            call = con.prepareCall("{call checkLogin(?,?)}");
            call.setString(1, email);
            call.setString(2, password);

            rs = call.executeQuery();
            while (rs.next()) {
                return true;
            }

        } catch (SQLException e) {
            Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (call != null) {
                    call.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                Logger.getLogger(DBUtils.class.getName()).log(Level.SEVERE, null, e);
            }
        }
        return false;
    }
}
